package cmd

import (
	"MsSqlSync2PostgreSql/connect"
	"bufio"
	"database/sql"
	"encoding/json"
	"fmt"
	//_ "github.com/go-sql-driver/PostgreSql"
	"github.com/spf13/viper"
	"os"
	"strconv"
	"time"
	//_ "github.com/sijms/go-ora/v2"
	_ "github.com/lib/pq"
	_ "github.com/microsoft/go-mssqldb"
)

var srcDb *sql.DB
var destDb *sql.DB

func getConn() (connStr *connect.DbConnStr) {
	connStr = new(connect.DbConnStr)
	connStr.SrcHost = viper.GetString("src.host")
	connStr.SrcUserName = viper.GetString("src.username")
	connStr.SrcPassword = viper.GetString("src.password")
	connStr.SrcDatabase = viper.GetString("src.database")
	connStr.SrcSchema = viper.GetString("src.schema")
	connStr.SrcPort = viper.GetUint64("src.port")
	connStr.SrcParams = viper.GetStringMapString("src.params")
	connStr.DestHost = viper.GetString("dest.host")
	connStr.DestPort = viper.GetInt("dest.port")
	connStr.DestUserName = viper.GetString("dest.username")
	connStr.DestPassword = viper.GetString("dest.password")
	connStr.DestDatabase = viper.GetString("dest.database")
	connStr.DestParams = viper.GetStringMapString("dest.params")
	return connStr
}

func PrepareSrc(connStr *connect.DbConnStr) {
	// 生成源库连接
	//msdsnConfig := msdsn.Config{
	//	Port: connStr.SrcPort,
	//	Host: connStr.SrcHost,
	//	//Instance:   "",
	//	Database:   connStr.SrcDatabase,
	//	User:       connStr.SrcUserName,
	//	Password:   connStr.SrcPassword,
	//	Encryption: 0,
	//	Parameters: connStr.SrcParams,
	//	//TLSConfig:                 nil,
	//	//FailOverPartner:           "",
	//	//FailOverPort:              0,
	//	//HostInCertificateProvided: false,
	//	//ReadOnlyIntent:            false,
	//	//LogFlags:                  0,
	//	//ServerSPN:                 "",
	//	//Workstation:               "",
	//	//AppName:                   "",
	//	//DisableRetry:              false,
	//	//DialTimeout:               0,
	//	//ConnTimeout:               0,
	//	//KeepAlive:                 0,
	//	//PacketSize:                0,
	//	//Parameters:                nil,
	//	//Protocols:                 nil,
	//	//ProtocolParameters:        nil,
	//	//BrowserMessage:            0,
	//	//ChangePassword:            "",
	//	//ColumnEncryption:          false,
	//	//MultiSubnetFailover:       false,
	//}
	//mssqlConnStr := mssql.NewConnectorConfig(msdsnConfig)
	var err error
	mssqlConnStr := fmt.Sprintf("server=%s;port=%d;database=%s;user id=%s;password=%s;trustservercertificate=true;encrypt=DISABLE",
		connStr.SrcHost, connStr.SrcPort, connStr.SrcDatabase, connStr.SrcUserName, connStr.SrcPassword)
	srcDb, err = sql.Open("sqlserver", mssqlConnStr)
	if err != nil {
		log.Fatal("please check SourceDB yml file", err)
	}
	c := srcDb.Ping()
	if c != nil {
		log.Fatal("connect Source database failed ", c)
	}
	srcDb.SetConnMaxLifetime(2 * time.Hour) // 一个连接被使用的最长时间，过一段时间之后会被强制回收
	srcDb.SetMaxIdleConns(0)                // 最大空闲连接数，0为不限制
	srcDb.SetMaxOpenConns(0)                // 设置连接池最大连接数
	log.Info("connect Source ", connStr.SrcHost, " success")
}

func PrepareDest(connStr *connect.DbConnStr) {
	// 生成目标连接
	destHost := connStr.DestHost
	destUserName := connStr.DestUserName
	destPassword := connStr.DestPassword
	destDatabase := connStr.DestDatabase
	destPort := connStr.DestPort
	destConn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", destHost, destPort, destUserName, destPassword, destDatabase)
	var err error
	destDb, err = sql.Open("postgres", destConn)
	if err != nil {
		log.Fatal("please check PostgreSql yml file", err)
	}

	c := destDb.Ping()
	if c != nil {
		log.Fatal("connect target PostgreSql failed ", c)
	}
	destDb.SetConnMaxLifetime(2 * time.Hour) // 一个连接被使用的最长时间，过一段时间之后会被强制回收
	destDb.SetMaxIdleConns(0)                // 最大空闲连接数，0为不限制
	destDb.SetMaxOpenConns(0)                // 设置连接池最大连接数
	log.Info("connect PostgreSql ", destHost, " success")
}

func LogError(logDir string, logName string, strContent string, errInfo error) {
	f, errFile := os.OpenFile(logDir+"/"+logName+".log", os.O_CREATE|os.O_APPEND|os.O_RDWR, os.ModePerm)
	if errFile != nil {
		log.Fatal(errFile)
	}
	defer func() {
		if errFile := f.Close(); errFile != nil {
			log.Fatal(errFile) // 或设置到函数返回值中
		}
	}()
	// create new buffer
	buffer := bufio.NewWriter(f)
	_, errFile = buffer.WriteString(strContent + " -- ErrorInfo " + StrVal(errInfo) + "\n")
	if errFile != nil {
		log.Fatal(errFile)
	}
	// flush buffered data to the file
	if errFile := buffer.Flush(); errFile != nil {
		log.Fatal(errFile)
	}
}

func LogOutput(logDir string, logName string, strContent string) {
	f, errFile := os.OpenFile(logDir+"/"+logName+".log", os.O_CREATE|os.O_APPEND|os.O_RDWR, os.ModePerm)
	if errFile != nil {
		log.Fatal(errFile)
	}
	defer func() {
		if errFile := f.Close(); errFile != nil {
			log.Fatal(errFile) // 或设置到函数返回值中
		}
	}()
	// create new buffer
	buffer := bufio.NewWriter(f)
	_, errFile = buffer.WriteString(strContent + "\n")
	if errFile != nil {
		log.Fatal(errFile)
	}
	// flush buffered data to the file
	if errFile := buffer.Flush(); errFile != nil {
		log.Fatal(errFile)
	}
}

// StrVal
// 获取变量的字符串值，目前用于interface类型转成字符串类型
// 浮点型 3.0将会转换成字符串3, "3"
// 非数值或字符类型的变量将会被转换成JSON格式字符串
func StrVal(value interface{}) string {
	var key string
	if value == nil {
		return key
	}

	switch value.(type) {
	case float64:
		ft := value.(float64)
		key = strconv.FormatFloat(ft, 'f', -1, 64)
	case float32:
		ft := value.(float32)
		key = strconv.FormatFloat(float64(ft), 'f', -1, 64)
	case int:
		it := value.(int)
		key = strconv.Itoa(it)
	case uint:
		it := value.(uint)
		key = strconv.Itoa(int(it))
	case int8:
		it := value.(int8)
		key = strconv.Itoa(int(it))
	case uint8:
		it := value.(uint8)
		key = strconv.Itoa(int(it))
	case int16:
		it := value.(int16)
		key = strconv.Itoa(int(it))
	case uint16:
		it := value.(uint16)
		key = strconv.Itoa(int(it))
	case int32:
		it := value.(int32)
		key = strconv.Itoa(int(it))
	case uint32:
		it := value.(uint32)
		key = strconv.Itoa(int(it))
	case int64:
		it := value.(int64)
		key = strconv.FormatInt(it, 10)
	case uint64:
		it := value.(uint64)
		key = strconv.FormatUint(it, 10)
	case string:
		key = value.(string)
	case []byte:
		key = string(value.([]byte))
	default:
		newValue, _ := json.Marshal(value)
		key = string(newValue)
	}

	return key
}

func cleanDBconn() {
	// 遍历正在执行的客户端，使用kill query 命令kill所有查询id,避免目标数据库仍在执行额外sql
	rows, err := destDb.Query("SELECT pid FROM pg_stat_activity WHERE query like '/* goapp%';")
	if err != nil {
		log.Error(err)
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		err = rows.Scan(&id)
		if err != nil {
			log.Error("rows.Scan(&id) failed!", err)
		}
		destDb.Exec("kill query " + id)
		log.Info("kill thread id ", id)
	}
}

// 监控来自终端的信号，如果按下了ctrl+c，断开数据库查询以及退出程序
func exitHandle(exitChan chan os.Signal) {

	for {
		select {
		case sig := <-exitChan:
			fmt.Println("receive system signal:", sig)
			cleanDBconn() // 调用清理数据库连接的方法
			os.Exit(1)    //如果ctrl+c 关不掉程序，使用os.Exit强行关掉
		}
	}

}
